import os
import urllib
import time
import win32com.client as win32
import requests
from robot.api import logger
from web.ar3_excel import *

def ar3_export(url):
    #导出excel
    filepath = os.path.join(CURRENT_PATH, AR3_EXPORT_XLS)
    if os.path.exists(filepath):
        os.remove(filepath)
    logger.info('url: %s' % url)
    data = urllib.request.urlopen(url).read()
    with open(filepath, 'wb') as f:
        f.write(data)
    return filepath

def xlsx_change_xls(excel_type):
    #xlsx和xls相互转换
    #type是需要转换的类型，xlsx——>xlsx，xls——>xls
    filepath_xls = os.path.join(CURRENT_PATH, AR3_EXPORT_XLS)
    file_xlsx = os.path.join(CURRENT_PATH, AR3_EXPORT_XLSX)
    try:
        if excel_type == 'xls':
            excel = win32.gencache.EnsureDispatch('Excel.Application')
            wb = excel.Workbooks.Open(file_xlsx)
            if os.path.exists(filepath_xls):
                    os.remove(filepath_xls)
            wb.SaveAs(filepath_xls, FileFormat = 56)
            wb.Close()
            excel.Application.Quit()
            return filepath_xls
        elif excel_type == 'xlsx':
            excel = win32.gencache.EnsureDispatch('Excel.Application')
            wb = excel.Workbooks.Open(filepath_xls)
            if os.path.exists(file_xlsx):
                    os.remove(file_xlsx)
            wb.SaveAs(file_xlsx, FileFormat = 51)
            wb.Close()
            excel.Application.Quit()
            return file_xlsx
    except Exception as e:
        raise("xlsx_change_xls with error:%s" % e) 

def ar3_check_macsnImport(filepath):
    #检查注册设备，批量增加,下载模板是否正确
    with ar3_open_excel(filepath) as wb:
            a = ['mac','sn','name']
            b = []
            if len(ar3_get_sheets(wb)) == 1:
                sheet_name =  ar3_get_sheets(wb)[0]
                sheet = ar3_open_sheet(wb,sheet_name)
                column = ar3_get_max_column(sheet)
                for i in range(1,column+1):
                    ret = ar3_read_cell_by_index(sheet,1,i)
                    if ret == a[i-1]:
                        b.append(a[i-1])
                        continue
                    else:
                        raise Exception('The {} is not == {}'.format(ret,a[i-1]))
            else:
                raise Exception('The macsnImport is error,please check registration equipmentr excel')
            return 'The macsnImport is {}'.format(b)

def ar3_write_macsnImport(data):
    convert_data = ar3_check_writedata(data)
    if len(convert_data) != 3:
        raise Exception(u"传入的data数据长度不对 ：data： {}".format(convert_data))
    filepath = os.path.join(CURRENT_PATH, AR3_EXPORT_XLSX)
    sheet_name = 'Sheet1'
    with ar3_open_excel(filepath) as wb:
        sheet = ar3_open_sheet(wb,sheet_name)
        columnindex = 0
        #写入Mac地址列
        columnindex += 1
        data = convert_data[u'mac']
        data.insert(0, u'mac')
        logger.info(u'mac地址列数据: %s' % data)
        ar3_write_column_data(sheet, columnindex, data)
        #写入sn
        columnindex += 1
        data = convert_data[u'sn']
        data.insert(0, u'sn')
        logger.info(u'sn数据: %s' % data)
        ar3_write_column_data(sheet, columnindex, data)
        #写入name
        columnindex += 1
        data = convert_data[u'name']
        data.insert(0, u'name')
        logger.info(u'name数据: %s' % data)
        ar3_write_column_data(sheet, columnindex, data)

def ar3_import(url,filepath=None,TK=None):
    #导入excel表
    content = None
    headers = {"Cookie": "TK={}".format(TK)}
    if os.path.exists(filepath):
        with open(filepath, 'rb') as f:
            s = requests.post(url, headers = headers, files = {'file': f})
            content = s.content
    else:
        raise Exception("The filepath is not find")
    return content.decode('utf-8')